package com.wolfpire.workflow.dao.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.workflow.dao.ProInstanceDao;
import com.wolfpire.workflow.om.ProInstance;

/**
 * 流程实例
 * 
 * @author liushx
 * 
 */
@Repository("proInstanceDao")
public class ProInstanceDaoImpl extends BaseHibernateDao<ProInstance, Long> implements ProInstanceDao {

	public ProInstance getProByNameId(final String nameId) {
		
		return (ProInstance) this.createCriteria().add(Restrictions.eq("nameId", nameId)).uniqueResult();
	}

	/**
	 * 根据实例ID获取实例信息
	 * @param instanceId
	 * @return 
	 */
	public ProInstance getProByInstanceId(final Long instanceId) {
	
		return (ProInstance) this.createCriteria().add(Restrictions.eq("instanceId", instanceId)).uniqueResult();
	}
	
	/**
	 * 根据实例ID获取实例信息
	 * @param instanceId
	 * @return
	 */
	
	@SuppressWarnings("unchecked")
	public Map<String, Object> getProMapByInstanceId(final Long instanceId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T2.INSTANCE_ID,T2.FORM_ID,T2.FORM_TYPE_ID, T2.USER_ID,T2.USER_NAME, T2.ORG_NAME");
		sql.append(" FROM  WF_PROCESS_INSTANCE T2 ");
		sql.append(" WHERE  T2.INSTANCE_ID = ").append(instanceId);
				
		Query query = getSession().createSQLQuery(sql.toString());
		final List<Map<String, Object>> lsMap = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if(lsMap == null || lsMap.isEmpty() ){
			return null;
		}
	//	this.createCriteria().add(Restrictions.eq("instanceId", instanceId)).setResultTransformer(arg0)
		return lsMap.get(0);
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryStatisticalList(final String wftypeId) {
		StringBuffer sql = new StringBuffer("SELECT T1.PROC_NAME, SUM(DECODE(T1.STATUS, '1', T1.PRONUM, 0)) AS UNPASS, SUM(DECODE(T1.STATUS, '2', T1.PRONUM, 0)) AS PASS ");
		sql.append(" FROM (SELECT T.DEPLOYMENT_ID, T.PROC_NAME, T.STATUS, COUNT(T.STATUS) AS PRONUM FROM WF_PROCESS_INSTANCE T ");
		sql.append(" LEFT JOIN WF_WORKFLOW T2 ON T.DEPLOYMENT_ID=T2.DEPLOYMENT_ID WHERE T2.WFTYPEID=? ");
		sql.append(" GROUP BY T.DEPLOYMENT_ID, T.PROC_NAME, T.STATUS) T1 GROUP BY T1.PROC_NAME");

		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, wftypeId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	/**
	 * 根据流程名称查流程
	 * @param proName
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryStatisticalListByProName(String proName) {

		String sql = "SELECT distinct (T2.INSTANCE_ID), T.DBID_,T.PROCINST_, T.EXECUTION_ ,T2.TIME_LIMIT, T2.PROC_NAME, T2.FORM_ID, T2.FORM_TYPE_ID, T2.USER_ID, T2.USER_NAME,T2.ORG_NAME,"
				+ " T2.CUR_NODE, T2.CUR_USER, TO_CHAR(T2.START_TIME, 'yyyy-mm-dd HH:mi:ss') AS START_TIME_,"
				+ " TO_CHAR(T2.END_TIME, 'yyyy-mm-dd HH:mi:ss') AS END_TIME_, T2.DEPLOYMENT_ID, "
				+ " T2.NAME_ID, T2.WORKFLOW_ID, T2.ESTATUSCODE, T2.ESTATUSNAME, T2.EVENTOPTYPE, R.STATUS "
				+ " FROM  JBPM4_TASK T LEFT JOIN  JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "
				+ " JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID "
				+ " AND T2.STATUS IN('1', '2')";
		if (proName != null && !"".equals(proName)) {
			sql += " AND T2.PROC_NAME LIKE ?";
		}

		sql += " LEFT JOIN  WF_REMIND R " + " ON R.INSTANCE_ID = T2.INSTANCE_ID ";
		sql += " ORDER BY T2.INSTANCE_ID DESC ";
		Query query = getSession().createSQLQuery(sql);
		if (null != proName && !proName.trim().equals("")) {
			proName = "%" + proName + "%";
			query.setParameter(0, proName);
		}
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryListByNotTimeOut() {

		String sql = "SELECT distinct (T2.INSTANCE_ID), T.DBID_,T.PROCINST_, T.EXECUTION_ ,T2.TIME_LIMIT, T2.PROC_NAME, T2.FORM_ID, T2.FORM_TYPE_ID, T2.USER_ID, T2.USER_NAME,T2.ORG_NAME,"
				+ " T2.CUR_NODE, T2.CUR_USER, TO_CHAR(T2.START_TIME, 'yyyy-mm-dd HH:mi:ss') AS START_TIME_,"
				+ " TO_CHAR(T2.END_TIME, 'yyyy-mm-dd HH:mi:ss') AS END_TIME_, T2.DEPLOYMENT_ID, "
				+ " T2.NAME_ID, T2.WORKFLOW_ID, T2.ESTATUSCODE, T2.ESTATUSNAME, T2.EVENTOPTYPE "
				+ " FROM  JBPM4_TASK T LEFT JOIN  JBPM4_PARTICIPATION T1 ON T.DBID_=T1.TASK_ "
				+ " JOIN  WF_PROCESS_INSTANCE T2  ON T.PROCINST_ = T2.INSTANCE_ID "
				+" AND ( T2.TIME_LIMIT_STATUS IS NULL OR T2.TIME_LIMIT_STATUS IN ('1','2') ) ";

		Query query = getSession().createSQLQuery(sql);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

	}
	
	public int countProInstanceTimeout(final String timeout) {

		String sql = "SELECT COUNT(*) taskunm FROM  WF_PROCESS_INSTANCE T2 WHERE T2.TIME_LIMIT_STATUS=? ";

		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, timeout);
		
		return Integer.valueOf(String.valueOf(query.uniqueResult()));
	}
	
	
	public int countSubInstancesByParentIdAndStatus(Long parentId, String status) {
		String sql = "select count(*) from  wf_process_instance t where t.parent_id=? and t.status=?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, parentId);
		query.setParameter(1, status);
		return Integer.valueOf(String.valueOf(query.uniqueResult()));
	}
	
	public void updateCombination(String instanceIds){
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE WF_PROCESS_INSTANCE T SET T.STATUS='2' WHERE 1=1 AND ")
		.append(" T.INSTANCE_ID IN(")
		.append(instanceIds)
		.append(") ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.executeUpdate();
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryCombinationInstance(String combinationDemandIds,String formType) {

		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT T.INSTANCE_ID FROM WF_PROCESS_INSTANCE T ")
		.append(" WHERE T.FORM_TYPE_ID = ").append(formType)
		.append(" AND T.FORM_ID IN(").append(combinationDemandIds).append(") ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

	}
	
	@SuppressWarnings("unchecked")
	public Map<String, Object> getInstance(String formId,String formTypeId) {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T.INSTANCE_ID FROM WF_PROCESS_INSTANCE T ")
		.append(" WHERE T.FORM_TYPE_ID = ").append(formTypeId)
		.append(" AND T.FORM_ID = ").append(formId).append(" ");
				
		Query query = getSession().createSQLQuery(sql.toString());
		final List<Map<String, Object>> lsMap = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		if(lsMap == null || lsMap.isEmpty() ){
			return null;
		}
		return lsMap.get(0);
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> querysubInstance(String parentId,
			String formTypeId, String startTime, String endTime) {

		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT T.INSTANCE_ID FROM WF_PROCESS_INSTANCE T ")
		.append(" WHERE T.FORM_TYPE_ID = ").append(formTypeId)
		.append(" AND T.PARENT_ID =").append(parentId).append(" ");
		if (!StringUtils.isBlank(startTime)){
			sql.append(" AND TO_DATE(TO_CHAR(T.START_TIME, 'YYYY-MM-DD'), 'YYYY-MM-DD')>=to_date('" + startTime.trim() + "', 'yyyy-MM-dd') ");
		}
		if (!StringUtils.isBlank(endTime)){
			sql.append(" AND TO_DATE(TO_CHAR(T.END_TIME, 'YYYY-MM-DD'), 'YYYY-MM-DD')<=to_date('" + endTime.trim() + "', 'yyyy-MM-dd') ");
		}
		
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

	}
	
	public List<Map<String, Object>> querysubInstance(String taskId){
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT T.INSTANCE_ID FROM WF_PROCESS_INSTANCE T ")
		.append(" WHERE T.TASK_ID = ").append(taskId)
		.append(" AND T.PARENT_ID IS NOT NULL ");
		
		Query query = getSession().createSQLQuery(sql.toString());
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	public void invalidInstance(String instanceId){
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE WF_PROCESS_INSTANCE T SET T.STATUS='4' WHERE 1=1 AND ")
		.append(" ( T.INSTANCE_ID =")
		.append(instanceId)
		.append(" OR T.PARENT_ID =")
		.append(instanceId)
		.append(" ) ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.executeUpdate();
	}
	
	public ProInstance getProByFormId(Long formId, Long formTypeId) {
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.eq("formId", formId));
		criteria.add(Restrictions.eq("formTypeId",formTypeId));
		return (ProInstance) criteria.uniqueResult();
	}
}